package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import utils.util;
import connection.ConnectionManager;
import domain.Comment;

public class JDBCCommentDAO implements ICommentDAO{

	Connection conn;
	
	public List<Comment> getAllComments() {
		List<Comment> comments = new LinkedList<Comment>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT c.comid, u.login, c.video_usuarios_video_vid, c.comentario, c.fecha " +
					"FROM comentarios c INNER JOIN usuarios u ON u.uid=c.video_usuarios_usuarios_uid;");
			Comment comment = null;
			while(rs.next()){
				comment = new Comment(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),util.ConvertDateToString(rs.getObject(5)));
				comments.add(comment);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comments;
	}
	
	public List<Comment> getAllCommentsByUser(String loginUser) {
		List<Comment> comments = new LinkedList<Comment>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT c.comid, u.login, c.video_usuarios_video_vid, c.comentario, c.fecha " +
					"FROM comentarios c INNER JOIN usuarios u ON u.uid=c.video_usuarios_usuarios_uid WHERE u.login='"+loginUser+"';");
			Comment comment = null;
			while(rs.next()){
				comment = new Comment(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),util.ConvertDateToString(rs.getObject(5)));
				comments.add(comment);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comments;
	}
	
	public List<Comment> getAllCommentsByVideo(Integer idVideo) {
		List<Comment> comments = new LinkedList<Comment>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT c.comid, u.login, c.video_usuarios_video_vid, c.comentario, c.fecha " +
					"FROM comentarios c INNER JOIN usuarios u ON u.uid=c.video_usuarios_usuarios_uid WHERE c.video_usuarios_video_vid = '" + idVideo + "' " +
							"ORDER BY c.fecha DESC;");
			Comment comment = null;
			while(rs.next()){
				comment = new Comment(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),util.ConvertDateToString(rs.getObject(5)));
				comments.add(comment);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comments;
	}
	
	public Comment getCommentsById(Integer idComentario) {
		Comment comment = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT c.comid, u.login, c.video_usuarios_video_vid, c.comentario, c.fecha " +
					"FROM comentarios c INNER JOIN usuarios u ON u.uid=c.video_usuarios_usuarios_uid WHERE c.comid = " + idComentario + "");
			if(rs.next()){
				comment = new Comment(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),util.ConvertDateToString(rs.getObject(5)));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comment;
	}
	
	public Comment getLastComentByUser(Integer UserId) {
		Comment comment = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT c.comid, u.login, c.video_usuarios_video_vid, c.comentario, c.fecha " +
					"FROM comentarios c INNER JOIN usuarios u ON u.uid=c.video_usuarios_usuarios_uid WHERE u.uid="+UserId+" ORDER BY c.fecha DESC LIMIT 1;");
			while(rs.next()){
				comment = new Comment(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),util.ConvertDateToString(rs.getObject(5)));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comment;
	}
	
	public void insertComment(Integer idVideo, Integer idUser, String comentario){
		try{
			conn= ConnectionManager.getInstance().openConnection();
			Statement stmt=conn.createStatement();
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	        Date date = new Date();  
			stmt.executeUpdate("INSERT INTO comentarios (video_usuarios_usuarios_uid, video_usuarios_video_vid, comentario, fecha)" +
					" VALUES ("+idUser+","+idVideo+",'"+comentario+"','"+dateFormat.format(date)+"');");
			stmt.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
		
}
